package cn.backpackerxl.dao.impl;

import cn.backpackerxl.dao.StudentDao;
import cn.backpackerxl.entity.Student;
import cn.backpackerxl.pojo.PageStudent;
import cn.backpackerxl.util.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: backpackerxl
 * @create: 2021/12/8
 * @filename: StudentDaoImpl
 **/
public class StudentDaoImpl implements StudentDao {
    private Connection cn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    private Student student = null;
    private PageStudent pageStudent = null;
    private String sql = null;

    @Override
    public PageStudent findAll(int current, int pageSize) {
        sql = "select * from t_student limit ?,?";
        pageStudent = new PageStudent();
        pageStudent.setStudentList(findByStudent(current, pageSize));
        pageStudent.setTotalStudent(totalStudent());
        return pageStudent;
    }

    /**
     * 查询总的学生数
     *
     * @return 返回查询数量
     */
    private int totalStudent() {
        int totalStudent = 0;
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement("select count(*) from t_student");
            rs = ps.executeQuery();
            if (rs.next()) {
                totalStudent = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps, rs);
        }
        return totalStudent;
    }

    @Override
    public int addStudent(Student student) {
        sql = "insert into t_student values(id,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setNString(1, student.getStuId());
            ps.setNString(2, student.getName());
            ps.setInt(3, Integer.parseInt(student.getSex()));
            ps.setInt(4, student.getAge());
            ps.setInt(5, student.getHeight());
            ps.setInt(6, student.getWeight());
            ps.setNString(7, student.getHobby());
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps);
        }
        return i;
    }

    @Override
    public PageStudent findByName(String name, int current, int pageSize) {
        sql = "select * from t_student where name like '%" + name + "%' limit ?,?";
        pageStudent = new PageStudent();
        pageStudent.setStudentList(findByStudent(current, pageSize));
        pageStudent.setTotalStudent(searchTotal(name));
        return pageStudent;
    }

    /**
     * 查询出来的总的结果数
     *
     * @param name 查询条件
     * @return 返回符合条件的数量
     */
    private int searchTotal(String name) {
        sql = "select count(*) from t_student where name like '%" + name + "%'";
        int searchTotalStudent = 0;
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                searchTotalStudent = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps, rs);
        }
        return searchTotalStudent;
    }

    @Override
    public int updateStudent(Student student) {
        sql = "update t_student set stu_id=?,name=?,sex=?,age=?,height=?,weight=?,hobby=? where id = ?";
        int i = 0;
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setNString(1, student.getStuId());
            ps.setNString(2, student.getName());
            ps.setInt(3, Integer.parseInt(student.getSex()));
            ps.setInt(4, student.getAge());
            ps.setInt(5, student.getHeight());
            ps.setInt(6, student.getWeight());
            ps.setNString(7, student.getHobby());
            ps.setInt(8, student.getId());
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps);
        }
        return i;
    }

    @Override
    public int deleteStudent(int id) {
        int i = 0;
        sql = "delete from t_student where id=?";
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps);
        }
        return i;
    }

    @Override
    public Student findById(int id) {
        sql = "select * from t_student where id = ?";
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                int ids = rs.getInt(1);
                String stuId = rs.getString(2);
                String name = rs.getString(3);
                int sex = rs.getInt(4);
                int age = rs.getInt(5);
                int height = rs.getInt(6);
                int weight = rs.getInt(7);
                String hobby = rs.getString(8);
                String sexs = sex == 0 ? "男" : "女";
                student = new Student(ids, stuId, name, sexs, age, height, weight, hobby);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps, rs);
        }
        return student;
    }

    @Override
    public List<Student> downloadStudentInfo() {
        List<Student> download = new ArrayList<>();
        sql = "select stu_id,name,sex,age,height,weight,hobby from t_student";
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String stuId = rs.getString(1);
                String name = rs.getString(2);
                int sex = rs.getInt(3);
                int age = rs.getInt(4);
                int height = rs.getInt(5);
                int weight = rs.getInt(6);
                String hobby = rs.getString(7);
                String sexs = sex == 0 ? "男" : "女";
                download.add(new Student(stuId, name, sexs, age, height, weight, hobby));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps, rs);
        }
        return download;
    }

    private List<Student> findByStudent(int current, int pageSize) {
        List<Student> students = new ArrayList<>();
        try {
            cn = DButils.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setInt(1, (current - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String stuId = rs.getString(2);
                String name = rs.getString(3);
                int sex = rs.getInt(4);
                int age = rs.getInt(5);
                int height = rs.getInt(6);
                int weight = rs.getInt(7);
                String hobby = rs.getString(8);
                String sexs = sex == 0 ? "男" : "女";
                student = new Student(id, stuId, name, sexs, age, height, weight, hobby);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(cn, ps, rs);
        }
        return students;
    }
}
